package com.lovo.roomProject.room.dao.impl;

import com.lovo.roomProject.dao.BaseDAO;
import com.lovo.roomProject.pojo.ClaszDO;
import com.lovo.roomProject.pojo.CutPageBean;
import com.lovo.roomProject.pojo.RoomDO;
import com.lovo.roomProject.pojo.StudentDO;
import com.lovo.roomProject.room.dao.RoomDAO;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class RoomDAOImpl extends BaseDAO implements RoomDAO {
    private RoomDO fillRoom() throws SQLException {
        RoomDO roomDO = new RoomDO();
        roomDO.setId(rs.getInt("r_id"));
        roomDO.setAddr(rs.getString("r_addr"));
        roomDO.setCount(rs.getInt("r_count"));
        roomDO.setStatus(rs.getString("r_status"));
        roomDO.setType(rs.getString("r_type"));
        roomDO.setInCount(rs.getInt("count(s.s_id)"));
        return roomDO;
    }

    @Override
    public List listRoom() {
        this.getConnection();
        String sql = "select r.*,count(s.s_id) " +
                "from t_room r left join t_student s on r.r_id=s.s_r_id " +
                "group by r.r_id ;";
        List list = new ArrayList();
        try {
            statement = con.prepareStatement(sql);
            rs = statement.executeQuery();
            while (rs.next()) {
                RoomDO roomDO = fillRoom();
                list.add(roomDO);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            closeConnection();
        }
        return list;
    }

    @Override
    public List listRoomByType(String type) {
        this.getConnection();
        String sql = "select r.*,count(s.s_id) from t_room r left join t_student s on r.r_id=s.s_r_id" +
                " where r.r_type=?" +
                " group by r.r_id,r_addr" +
                " having count(s.s_id)<r.r_count";
        List list = new ArrayList();
        try {
            statement = con.prepareStatement(sql);
            statement.setObject(1, type);
            rs = statement.executeQuery();
            while (rs.next()) {
                RoomDO roomDO = fillRoom();
                list.add(roomDO);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            closeConnection();
        }
        return list;
    }

    @Override
    public CutPageBean listRoomByPage(int current, String addr, String status, String enable, String type) {
        this.getConnection();
        String selectSql = "select r.*,count(s.s_id) ";
        String countSql = "select count(distinct r.r_id) cnt ";
        String fromSql = "from t_room r left join t_student s on r.r_id=s.s_r_id ";
        String groupBySql = "group by r.r_id ";
        String limitSql = "limit ?,?";

        String dynamicSql = "where 1=1 ";
        if (addr != null && !addr.equals("")) {
            dynamicSql += "and r.r_addr like '%" + addr + "%' ";
        }
        if (!status.equals("不限")) {
            dynamicSql += "and r.r_status = '" + status + "' ";
        }
        if (!type.equals("不限")) {
            dynamicSql += "and r.r_type ='" + type + "' ";
        }
        if (!enable.equals("不限")) {
            groupBySql += "having count(s.s_id)<r.r_count ";
        }


        CutPageBean cutPageBean = new CutPageBean();
        List list = new ArrayList();
        try {
            statement = con.prepareStatement(selectSql + fromSql + dynamicSql + groupBySql + limitSql);
            statement.setObject(1, (current - 1) * CutPageBean.PAGESIZE);
            statement.setObject(2, CutPageBean.PAGESIZE);
            rs = statement.executeQuery();
            while (rs.next()) {
                RoomDO roomDO = fillRoom();
                list.add(roomDO);
            }
            statement = con.prepareStatement(countSql + fromSql + dynamicSql);
            rs = statement.executeQuery();
            rs.next();
            cutPageBean.setCount(rs.getInt("cnt"));
            cutPageBean.setList(list);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            closeConnection();
        }
        return cutPageBean;
    }

    @Override
    public RoomDO getRoomById(int id) {
        this.getConnection();
        String sql = "select r.*,count(s.s_id) " +
                "from t_room r left join t_student s on r.r_id=s.s_r_id " +
                "where r.r_id=?";
        RoomDO roomDO = new RoomDO();
        try {
            statement = con.prepareStatement(sql);
            statement.setObject(1, id);
            rs = statement.executeQuery();
            if (rs.next()) {
                roomDO.setId(rs.getInt("r_id"));
                roomDO.setAddr(rs.getString("r_addr"));
                roomDO.setCount(rs.getInt("r_count"));
                roomDO.setStatus(rs.getString("r_status"));
                roomDO.setType(rs.getString("r_type"));
                roomDO.setInCount(rs.getInt("count(s.s_id)"));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            closeConnection();
        }

        return roomDO;
    }

    @Override
    public void insert(RoomDO roomDO) {
        this.getConnection();
        String sql = "insert into t_room(r_addr,r_count,r_type) value(?,?,?)";
        try {
            statement = con.prepareStatement(sql);
            statement.setObject(1, roomDO.getAddr());
            statement.setObject(2, roomDO.getCount());
            statement.setObject(3, roomDO.getType());
            statement.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            closeConnection();
        }
    }

    @Override
    public void delete(int id) {
        this.getConnection();
        String sql = "delete from t_room where r_id=?";
        try {
            statement = con.prepareStatement(sql);
            statement.setObject(1, id);
            statement.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            closeConnection();
        }
    }

    @Override
    public List listStudentById(int id) {
        this.getConnection();
        String sql = "select s.s_name,s.s_phone,c.c_id,c.c_name " +
                "from t_room r left join t_student s on r.r_id=s.s_r_id " +
                "left join t_class c on s.s_c_id=c.c_id " +
                "where r.r_id=?";
        List list = new ArrayList();
        try {
            statement = con.prepareStatement(sql);
            statement.setObject(1, id);
            rs = statement.executeQuery();
            while (rs.next()) {
                StudentDO studentDO = new StudentDO();
                studentDO.setName(rs.getString("s_name"));
                studentDO.setPhone(rs.getString("s_phone"));

                ClaszDO claszDO = new ClaszDO();
                claszDO.setId(rs.getInt("c_id"));
                claszDO.setName(rs.getString("c_name"));

                studentDO.setClasz(claszDO);
                list.add(studentDO);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            closeConnection();
        }
        return list;
    }

    @Override
    public void update(RoomDO roomDO) {
        this.getConnection();
        String sql = "update t_room set r_status=? where r_id=?";
        try {
            statement = con.prepareStatement(sql);
            statement.setObject(1, roomDO.getStatus());
            statement.setObject(2, roomDO.getId());
            statement.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            closeConnection();
        }
    }

}
